<?php
/***************************************************************************
 *                               dbcommon.php
 *                            -------------------
 *                      (APU) Automated Pilot Utility
 *   begin                : Sunday, Nov 12, 2006
 *   copyright            : (C) 2004-2006 SF Games
 *   email                : techsupport@sf-games.com
 ***************************************************************************/

	include_once('config.php');

				/**********************************************
				 * Common database functions for dealing with *
				 * APU specific tables                        *
				 **********************************************/

//
// Checks the user and password combo for validity
// The master password is also checked and if given, is used as a valid
// condition
//
function validateUser($user, $password)
{
	include('config.php');	// include_once no workie for some dumb reason here.
	$bResult = true;

	// Encrypt the password
	$pencrpyt = md5($password);

	// Performing SQL query to get the given pilot by ID
	$query  = 'SELECT PILOT_ID, PASSWORD FROM ' .$table_prefix .'PILOTS WHERE PILOT_ID = ' .sqlfix($user);
	$result = mysql_query($query);
	if ( !$result )
		die("Error: in validateUser() from dbcommon.php. Query for Pilot ID in table PILOTS failed.<br/>Click your browsers back button to try again.<br/>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " . mysql_error());
	$row = mysql_fetch_array($result, MYSQL_ASSOC);

	if ( $row["PILOT_ID"] != $user )
		$bResult = false;

	// Note that master password allows admin to add any pilots PIREP or change passwords
	if ( $row["PASSWORD"] != $pencrpyt /*|| $password != $masterPass */ )
		$bResult = false;

	$pencrpyt = "";

	return $bResult;
}

//
// Given a rank number, return an HTML string representing a proper image for this rank
// Function assumes a database connection has been established
//
function getRankGraphicFixedWing($rankID)
{
	global $table_prefix;

	$rankGfx = "";
	$rankQuery   = 'SELECT * FROM ' .$table_prefix .'FIXEDWING_AC_RANKS WHERE RANK_ID = ' .$rankID;
	$rank = mysql_query($rankQuery);

	if ( !$rank )
		die("Error: in getRankGraphicFixedWing() from dbcommon.php. Query for current pilots rank gfx failed.<br />Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

	$rankrow = mysql_fetch_row($rank);
	$rankGfx = "<img src=\"" .$rankrow[2] ."\" alt=\"" .$rankrow[1] ."\"/>";

	return $rankGfx;
}


//
// Given a rank number, return an HTML string representing the rank description
//
function getRankDescriptionFixedWing($rankID)
{
	global $table_prefix;

	$rankDescription = "";
	$rankQuery = 'SELECT * FROM ' .$table_prefix .'FIXEDWING_AC_RANKS WHERE RANK_ID = ' .$rankID;
	$rank = mysql_query($rankQuery);

	if ( !$rank )
		die("Error: in getRankDescriptionFixedWing() from dbcommon.php. Query for current pilots rank gfx failed.<br />Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

	$rankrow  = mysql_fetch_row($rank);
	return $rankrow[1];
}


//
// Given a rank number, return an HTML string representing a proper image for this rank
//
function getRankGraphicHelicopter($rankID)
{
	global $table_prefix;

	$rankGfx = "";
	$rankQuery = 'SELECT * FROM ' .$table_prefix .'HELO_AC_RANKS WHERE RANK_ID = ' .$rankID;
	$rank = mysql_query($rankQuery);

	if ( !$rank )
		die("Error: in getRankGraphicHelicopter() from dbcommon.php. Query for current pilots rank gfx failed.<br />Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

	$rankrow = mysql_fetch_row($rank);
	$rankGfx = "<img src=\"" .$rankrow[2] ."\" alt=\"" .$rankrow[1] ."\"/>";

	return $rankGfx;
}


//
// Given a rank number, return an HTML string representing the description for this rank
//
function getRankDescriptionHelicopter($rankID)
{
	global $table_prefix;

	$rankDescription = "";
	$rankQuery = 'SELECT * FROM ' .$table_prefix .'HELO_AC_RANKS WHERE RANK_ID = ' .$rankID;
	$rank = mysql_query($rankQuery);

	if ( !$rank )
		die("Error: in getRankDescriptionHelicopter() from dbcommon.php. Query for current pilots rank gfx failed.<br />Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

	$rankrow = mysql_fetch_row($rank);
	return $rankrow[1];
}


//
// Build a rank graphic string based on pilots information given
//
function buildRankGraphics($rankAC, $heloHrs, $rankHelo, $dart, $sar, $fleet, $bUseAwards)
{
	global $table_prefix;
	$rankGfx = "";

	// Everyone has an A/C rank
	$rankQueryA  = 'SELECT * FROM ' .$table_prefix .'FIXEDWING_AC_RANKS WHERE RANK_ID = ' .$rankAC;
	$rank = mysql_query($rankQueryA);

	if ( !$rank )
		die("Error: in buildRankGraphics() from dbcommon.php. Query for current pilots fixed wing rank gfx failed.<br>Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

	$rankrow  = mysql_fetch_row($rank);
	$rankGfx .= "<img src=\"" .$rankrow[2] ."\" alt=\"" .$rankrow[1] ."\"/>";

	// If we have helo hours, then we have a helo rank
	if ( $heloHrs > 0.0 )
	{
		$rankQueryH  = 'SELECT * FROM ' .$table_prefix .'HELO_AC_RANKS WHERE RANK_ID = ' .$rankHelo;
		$rank = mysql_query($rankQueryH);

		if ( !$rank )
			die("Error: in buildRankGraphics() from dbcommon.php. Query for current pilots helicopter rank gfx failed.<br>Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

		$rankrow  = mysql_fetch_row($rank);
		$rankGfx .= "<img src=\"" .$rankrow[2] ."\" alt=\"" .$rankrow[1] ."\"/>";
	}

	// Handle gfx for DART, SAR, and Fleet Support
	if ( $bUseAwards )
	{
		if ( $dart )
			$rankGfx .= "<img src=\"img/dart.jpg\" alt=\"DART Award\"/>";
		if ( $sar )
			$rankGfx .= "<img src=\"img/sar.gif\" alt=\"SAR Award\"/>";
		if ( $fleet )
			$rankGfx .= "<img src=\"img/fleetsupport.jpg\" alt=\"Fleet Support Award\"/>";
	}

	return $rankGfx;
}

//
// Return the rank number for the given number of hours
//
function getFixedWingRank($hours)
{
	include_once('config.php');
	global $table_prefix;

	$query  = 'SELECT * FROM ' .$table_prefix .'FIXEDWING_AC_RANKS';
	$result = mysql_query($query);
	if ( !$result )
		die("Error: in getFixedWingRank() from dbcommon.php. Query for ranks in FIXEDWING_AC_RANKS table failed" .mysql_error());

	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		// Assign the proper Rank
		if ( floatval($hours) >= floatval($row["MIN_HRS"]) )
		{
			$rank = $row["RANK_ID"];
		}
	}

	return $rank;
}

//
// Return the rank number for the given number of hours
//
function getHeloRank($hours)
{
	include_once('config.php');
	global $table_prefix;

	$query  = 'SELECT * FROM ' .$table_prefix .'FIXEDWING_AC_RANKS';
	$result = mysql_query($query);
	if ( !$result )
		die("Error: in getHeloRank() from dbcommon.php. Query for ranks in HELO_AC_RANKS table failed" .mysql_error());

	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		// Assign the proper Rank
		if ( floatval($hours) >= floatval($row["MIN_HRS"]) )
		{
			$rank = $row["RANK_ID"];
		}
	}

	return $rank;
}

//
// Gather a summation of PIREP on given pilot
//
function gatherPilotsPireps($pilotID)
{
	global $table_prefix;

	$pirepQuery  = "SELECT * FROM " .$table_prefix ."PIREP WHERE PILOT_ID = '";
	$pirepQuery .= $pilotID;
	$pirepQuery .= "'";
	$pirepQuery .= " ORDER BY FLIGHT_DATE";
	return gatherPireps($pirepQuery);
}

//
// Gather a summation of PIREP on given pilot and date range
//
function gatherPilotsPirepsByDate($pilotID, $beginDate, $endDate)
{
	global $table_prefix;

	$pirepQuery  = "SELECT * FROM " .$table_prefix ."PIREP WHERE PILOT_ID = '";
	$pirepQuery .= $pilotID;
	$pirepQuery .= "' AND FLIGHT_DATE >= '";
	$pirepQuery .= $beginDate;
	$pirepQuery .= "' AND FLIGHT_DATE <= '";
	$pirepQuery .= $endDate;
	$pirepQuery .= "'";
	$pirepQuery .= " ORDER BY FLIGHT_DATE";
	return gatherPireps($pirepQuery);
}

//
// Gather a summation of PIREP on given date range
//
function gatherPirepsByDate($beginDate, $endDate)
{
	global $table_prefix;

	$pirepQuery  = "SELECT * FROM " .$table_prefix ."PIREP WHERE FLIGHT_DATE >= '";
	$pirepQuery .= $beginDate;
	$pirepQuery .= "' AND FLIGHT_DATE <= '";
	$pirepQuery .= $endDate;
	$pirepQuery .= "'";
	$pirepQuery .= " ORDER BY FLIGHT_DATE";
	return gatherPireps($pirepQuery);
}

//
// Gather a summation of PIREP based on given query
//
function gatherPireps($query)
{
	global $PirepArray;
	global $table_prefix;

	$pirepArray = $PirepArray;
	$numPireps  = 0;
	$numPax     = 0;
	$numCargo   = 0;
	$fuelBurned = 0;
	$pilotHrs   = 0.0;
	$comments   = "";

	$pireps = mysql_query($query);
	if ( !$pireps )
		die("Error: in gatherPireps() from dbcommon.php. Query for current PIREP reports failed.<br />Click your browsers Refresh button to try again.<br />If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " .mysql_error());

	while ($pirepRow = mysql_fetch_array($pireps, MYSQL_ASSOC))
	{
		$numPireps++;
		$numPax     += $pirepRow["PAX"];
		$numCargo   += $pirepRow["CARGO"];
		$fuelBurned += $pirepRow["FUEL_BURN"];
		$pilotHrs   += ($pirepRow["TIME_HELO"] + $pirepRow["TIME_FIXED"]);
		$comments    = $pirepRow["COMMENTS"];
	}

	$pirepArray["PIREPS"]      = $numPireps;
	$pirepArray["PAX"]         = $numPax;
	$pirepArray["CARGO"]       = $numCargo;
	$pirepArray["FUEL_BURNED"] = $fuelBurned;
	$pirepArray["FLIGHT_HRS"]  = $pilotHrs;
	$pirepArray["COMMENTS"]    = $comments;

	return $pirepArray;
}

?>
